param($sourceServer, $sourceDatabase, $tableName)

## $skippedColumns = @{ "Cmdlet" = @("Name"); "CmdletHistory" = @("Name"); }
## $mappedColumns = @{ "Project" = @{ "OwnerAlias" = "Owner" }; "ProjectHistory" = @{ "OwnerAlias" = "Owner" }; }

$skippedColumns = @{ }
$mappedColumns = @{ }
$skipIdentity = @("Verb")

function Main
{
    GenerateSql $tableName (.\Invoke-SqlCommand $sourceServer $sourceDatabase "SELECT * FROM $tableName")
}

function GenerateSql([string] $tableName, $structuredData)
{
    $defaultDataRowProperties = "HasErrors","Item","ItemArray","RowError","RowState","Table"

    foreach($row in $structuredData)
    {
        $firstItem = $true

        $sqlStatement = ""
        if($skipIdentity -notcontains $tableName)
        {
            $sqlStatement += "SET IDENTITY_INSERT $tableName ON`n"
        }

        $sqlStatement += "INSERT INTO [$tableName]"
        $columnDefinition = "    ( "
        $valueDefinition = "VALUES`n    ( "

        foreach($property in $row.PsObject.Properties)
        {
            if($defaultDataRowProperties -contains $property.Name) { continue }
            if($skippedColumns[$tableName] -contains $property.Name) { continue }
            if($property.Value -is [DbNull]) { continue }

            if(-not $firstItem)
            {
                $columnDefinition += ", "
                $valueDefinition += ", "
            }

            if($mappedColumns.$tableName.($property.Name))
            {
                $columnDefinition += "[$($mappedColumns.$tableName.($property.Name))]"
            }
            else
            {
                $columnDefinition += "[$($property.Name)]"
            }

            $propertyString = [string] $property.Value
            if($property.Value -is [DateTime])
            {
                $propertyString = $property.Value.ToString("yyyy-MM-dd HH:mm:ss.fff")
            }

            $escapedValue = EscapeValue($propertyString)
            $valueDefinition += "'$escapedValue'"
            $firstItem = $false
        }

        $columnDefinition += " )"
        $valueDefinition += " )"

        $sqlStatement + "`n" + $columnDefinition + "`n" + $valueDefinition + "`n"
    }
}

function EscapeValue([string] $value)
{
    $escapedValue = $value.Replace("'", "''")
    $escapedValue = $escapedValue.Replace("\`n", "\\`n`n")
    $escapedValue
}

. Main